USE [ServerMonitor]
GO

/****** Object:  View [dbo].[AllServerDBSizeReport]    Script Date: 08/14/2012 13:30:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[AllServerDBSizeReport]
AS
  WITH AllServerLastWeekDBSize
  AS(SELECT A.ServerId,SN.Name,SN.IP,SN.Instance,A.[DBName]
      ,CAST([DataSize] * 8.0 / 1024 AS DECIMAL(10,2)) AS DataSizeMB
      ,CAST([DataUsage]/([DataSize] * 1.0) AS DECIMAL(10,2)) AS DataUsage
      ,(SELECT MIN([DiskFreeMB])
      FROM [ServerMonitor].[dbo].[AllServerDBFile]
      WHERE [ServerId] = A.ServerId AND [DBUsageId] = A.Id AND [FileType] = 'DATA') AS DataDiskMinFreeMB
      ,CAST([LogSize] * 8.0 / 1024 AS DECIMAL(10,2)) AS LogSizeMB
      ,CAST([LogUsage]/([LogSize] * 1.0) AS DECIMAL(10,2)) AS LogUsage   
      ,(SELECT MIN([DiskFreeMB])
      FROM [ServerMonitor].[dbo].[AllServerDBFile]
      WHERE [ServerId] = A.ServerId AND [DBUsageId] = A.Id AND [FileType] = 'LOG') AS LogDiskMinFreeMB      
      ,A.[RecordTime]
  FROM [ServerMonitor].[dbo].[AllServerDBUsage] AS A INNER JOIN
  (SELECT MAX(RecordTime) AS RecordTime,[ServerId],[DBName]
  FROM [ServerMonitor].[dbo].[AllServerDBUsage]
  WHERE DATEDIFF(DAY,RecordTime,GETDATE()) = 7
  GROUP BY [ServerId],[DBName]) AS B
  ON A.[RecordTime] = B.RecordTime AND A.[DBName] = B.DBName AND A.ServerId = B.ServerId
  LEFT JOIN [ServerMonitor].[dbo].[Server] AS SN
  ON A.ServerId = SN.Id)
  
  SELECT 
  A.Name,A.IP,
  B.Count AS LastC,A.Count,
  B.TotalDataSize AS LastTDS,B.DataPer AS LastDP,
  A.TotalDataSize,A.DataPer,
  A.Data - B.Data AS DataGrowthMB,
  B.TotalLogSize AS LastTLS,B.LogPer AS LastLP,
  A.TotalLogSize,A.LogPer,
  [ServerMonitor].[dbo].[DBDiff](A.[ServerId], A.RecordTime, B.RecordTime) AS Description
  FROM
  (SELECT [ServerId],[Name],[IP],COUNT(*) AS Count,MAX(RecordTime) AS RecordTime
        ,CAST(SUM([DataSizeMB]) / 1024.0 AS DECIMAL(10,2)) AS TotalDataSize
        ,CAST(SUM([DataSizeMB] * [DataUsage] * 1.0)AS DECIMAL(10,2)) AS Data
        ,CAST((SUM([DataSizeMB] * [DataUsage] * 1.0) * 1.0) / SUM([DataSizeMB]) AS DECIMAL(10,2)) AS DataPer
        ,CAST(SUM([LogSizeMB]) / 1024.0 AS DECIMAL(10,2)) AS TotalLogSize
        ,CAST((SUM([LogSizeMB] * [LogUsage] * 1.0) * 1.0) / SUM([LogSizeMB]) AS DECIMAL(10,2)) AS LogPer
  FROM [ServerMonitor].[dbo].[AllServerCurrentDBSize]
  WHERE [DBName] NOT IN ('tempdb','msdb','model','master')
  GROUP BY [ServerId],[Name],[IP]) AS A
  INNER JOIN
  (SELECT [ServerId],[Name],[IP],COUNT(*) AS Count,MAX(RecordTime) AS RecordTime
        ,CAST(SUM([DataSizeMB]) / 1024.0 AS DECIMAL(10,2)) AS TotalDataSize
        ,CAST(SUM([DataSizeMB] * [DataUsage] * 1.0)AS DECIMAL(10,2)) AS Data
        ,CAST((SUM([DataSizeMB] * [DataUsage] * 1.0) * 1.0) / SUM([DataSizeMB]) AS DECIMAL(10,2)) AS DataPer
        ,CAST(SUM([LogSizeMB]) / 1024.0 AS DECIMAL(10,2)) AS TotalLogSize
        ,CAST((SUM([LogSizeMB] * [LogUsage] * 1.0) * 1.0) / SUM([LogSizeMB]) AS DECIMAL(10,2)) AS LogPer
  FROM AllServerLastWeekDBSize
  WHERE [DBName] NOT IN ('tempdb','msdb','model','master')
  GROUP BY [ServerId],[Name],[IP]) AS B
  ON A.[ServerId] = B.[ServerId] AND A.Name = B.Name AND A.IP = B.IP

GO


